﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CuaHangMayTinh.BusinessEntities;
using CuaHangMayTinh.DataProvider;
using CuaHangMayTinh.Utilities;
using System.Data;

namespace CuaHangMayTinh.DataAccessLayer
{
    /// <summary>
    /// Lớp MatHangDA
    /// </summary>
    public class MatHangDA
    {
        public MatHangDA()
        {

        }

        /// <summary>
        /// Hàm Thêm 1 mặt hàng mới
        /// </summary>
        /// <param name="entity">Mặt hàng muốn thêm</param>
        /// <returns>0: không thêm được</returns>
        /// <returns>1: têm được</returns>
        public int Insert(MatHangEntity entity)
        {
            try
            {
                ParameterBuilder pb = DBFactory.CreateParamBuilder();

                pb.AddParameter("MaModelMH", entity.MaModelMH);
                pb.AddParameter("MaHoaDonNhap", entity.MaHoaDonNhap);
                pb.AddParameter("Serial_Code", entity.Serial_Code);
                return (int)DBFactory.Database.ExecuteNonQuery("MatHang_Insert", pb.Parameters);
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Hàm Thêm 1 mặt hàng mới, trả về ID
        /// </summary>
        /// <param name="entity">Mặt hàng muốn thêm</param>
        /// <returns>ID của mặt hàng mới thêm</returns>
        public int InsertGetID(MatHangEntity entity)
        {
            try
            {
                ParameterBuilder pb = DBFactory.CreateParamBuilder();

                pb.AddParameter("MaModelMH", entity.MaModelMH);
                pb.AddParameter("MaHoaDonNhap", entity.MaHoaDonNhap);
                pb.AddParameter("Serial_Code", entity.Serial_Code);
                return (int)DBFactory.Database.ExecuteNonQueryGetID("MatHang_Insert", pb.Parameters);
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Hàm Thêm 1 mặt hàng mới, dùng với transaction khi thêm 1 hóa đơn nhập
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="conn"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public static int InsertTS(MatHangEntity entity, DbConnector conn, System.Data.Common.DbTransaction tran)
        {
            try
            {
                ParameterBuilder pb = DBFactory.CreateParamBuilder();

                pb.AddParameter("MaModelMH", entity.MaModelMH);
                pb.AddParameter("MaHoaDonNhap", entity.MaHoaDonNhap);
                pb.AddParameter("Serial_Code", entity.Serial_Code);
                return (int)DBFactory.Database.ExecuteNonQueryGetID("MatHang_Insert", pb.Parameters, conn, tran);
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Hàm cập nhật 1 mặt hàng mới
        /// </summary>
        /// <param name="entity">Mặt hàng cần cập nhật</param>
        /// <returns></returns>
        public bool UpDate(MatHangEntity entity)
        {
            try
            {
                ParameterBuilder pb = DBFactory.CreateParamBuilder();
                pb.AddParameter("MaMH", entity.MaMH);
                pb.AddParameter("MaModelMH", entity.MaModelMH);
                pb.AddParameter("MaHoaDonNhap", entity.MaHoaDonNhap);
                pb.AddParameter("MaHoaDonBan", entity.MaHoaDonBan);
                pb.AddParameter("MaPhieuXuatKhoTraHang", entity.MaPhieuXuatKhoTraHang);
                pb.AddParameter("MaPhieuBaoHanhSuaChua", entity.MaPhieuBaoHanhSuaChua);
                pb.AddParameter("SerialCode", entity.Serial_Code);
                pb.AddParameter("TinhTrang", entity.TinhTrang);

                int numRowEffected = DBFactory.Database.ExecuteNonQuery("MatHang_UpDate", pb.Parameters);
                return (numRowEffected > 0);
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Hàm cập nhật 1 mặt hàng mới, dùng với transaction
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="conn"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public bool UpDate(MatHangEntity entity, DbConnector conn, System.Data.Common.DbTransaction tran)
        {
            try
            {
                ParameterBuilder pb = DBFactory.CreateParamBuilder();
                pb.AddParameter("MaMH", entity.MaMH);
                pb.AddParameter("MaModelMH", entity.MaModelMH);
                pb.AddParameter("MaHoaDonNhap", entity.MaHoaDonNhap);
                pb.AddParameter("MaHoaDonBan", entity.MaHoaDonBan);
                pb.AddParameter("MaPhieuXuatKhoTraHang", entity.MaPhieuXuatKhoTraHang);
                pb.AddParameter("MaPhieuBaoHanhSuaChua", entity.MaPhieuBaoHanhSuaChua);
                pb.AddParameter("SerialCode", entity.Serial_Code);
                pb.AddParameter("TinhTrang", entity.TinhTrang);

                int numRowEffected = DBFactory.Database.ExecuteNonQuery("MatHang_UpDate", pb.Parameters, conn, tran);
                return (numRowEffected > 0);
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Hàm cập nhật tình trạng của 1 mặt hàng khi đã bán
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="conn"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public static bool UpDateDaBanTS(MatHangEntity entity, DbConnector conn, System.Data.Common.DbTransaction tran)
        {
            try
            {
                ParameterBuilder pb = DBFactory.CreateParamBuilder();
                pb.AddParameter("MaMH", entity.MaMH);
                pb.AddParameter("MaHoaDonBan", entity.MaHoaDonBan);
                pb.AddParameter("TinhTrang", entity.TinhTrang);

                int numRowEffected = DBFactory.Database.ExecuteNonQuery("MatHang_Update_DaBan", pb.Parameters, conn, tran);
                return (numRowEffected > 0);
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Hàm cập nhật 1 mặt hàng khi đã trả hàng, dùng với transaction
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="conn"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public static bool UpDateDaTraTS(MatHangEntity entity, DbConnector conn, System.Data.Common.DbTransaction tran)
        {
            try
            {
                ParameterBuilder pb = DBFactory.CreateParamBuilder();
                pb.AddParameter("MaMH", entity.MaMH);
                pb.AddParameter("MaPhieuXuatKhoTraHang", entity.MaPhieuXuatKhoTraHang);
                pb.AddParameter("TinhTrang", entity.TinhTrang);

                int numRowEffected = DBFactory.Database.ExecuteNonQuery("MatHang_Update_DaTra", pb.Parameters, conn, tran);
                return (numRowEffected > 0);
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="conn"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public static bool UpDateBHSCTS(MatHangEntity entity, DbConnector conn, System.Data.Common.DbTransaction tran)
        {
            try
            {
                ParameterBuilder pb = DBFactory.CreateParamBuilder();
                pb.AddParameter("MaMH", entity.MaMH);
                pb.AddParameter("MaPhieuBaoHanhSuaChua", entity.MaPhieuBaoHanhSuaChua);
                pb.AddParameter("TinhTrang", entity.TinhTrang);

                int numRowEffected = DBFactory.Database.ExecuteNonQuery("MatHang_Update_BHSC", pb.Parameters, conn, tran);
                return (numRowEffected > 0);
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Hàm UpDate tình trạng của 1 mặt hàng
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool Update_TinhTrang(MatHangEntity entity)
        {
            try
            {
                ParameterBuilder pb = DBFactory.CreateParamBuilder();
                pb.AddParameter("MaMH", entity.MaMH);
                pb.AddParameter("TinhTrang", entity.TinhTrang);

                int numRowEffected = DBFactory.Database.ExecuteNonQuery("MatHang_Update_TinhTrang", pb.Parameters);
                return (numRowEffected > 0);
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool Update_Serial(MatHangEntity entity)
        {
            try
            {
                ParameterBuilder pb = DBFactory.CreateParamBuilder();
                pb.AddParameter("MaMH", entity.MaMH);
                pb.AddParameter("SerialCode", entity.Serial_Code);

                int numRowEffected = DBFactory.Database.ExecuteNonQuery("MatHang_Update_Serial", pb.Parameters);
                return (numRowEffected > 0);
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Hàm Lấy tất cả thông tin của mặt hàng
        /// </summary>
        /// <param name="View">Dùng View hay không</param>
        /// <returns>DataTable</returns>
        public DataTable Find_All(bool View)
        {
            try
            {
                DataTable dt = new DataTable();
                ParameterBuilder pb = DBFactory.CreateParamBuilder();
                pb.AddParameter("View", View);

                dt = DBFactory.Database.FillDataTable("MatHang_FindAll", pb.Parameters);
                return dt;
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Tìm mặt hàng bẳng serial của nó
        /// </summary>
        /// <param name="serial"></param>
        /// <param name="View"></param>
        /// <returns></returns>
        public DataTable Find_Serial(string serial, bool View)
        {
            try
            {
                DataTable dt = new DataTable();
                ParameterBuilder pb = DBFactory.CreateParamBuilder();
                pb.AddParameter("Serial_Code", serial);
                pb.AddParameter("View", View);
                dt = DBFactory.Database.FillDataTable("MatHang_Find_Serial", pb.Parameters);
                return dt;
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Hàm tìm mặt hàng bằng ID
        /// </summary>
        /// <param name="id"></param>
        /// <param name="View"></param>
        /// <returns></returns>
        public DataTable Find_ID(int id, bool View)
        {
            try
            {
                DataTable dt = new DataTable();
                ParameterBuilder pd = DBFactory.CreateParamBuilder();
                pd.AddParameter("ID", id);
                pd.AddParameter("View", View);

                dt = DBFactory.Database.FillDataTable("MatHang_Find_ID", pd.Parameters);
                return dt;
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Hàm tìm mặt hàng theo nhà cung cấp
        /// </summary>
        /// <param name="MaNCC"></param>
        /// <returns></returns>
        public DataTable Find_NhaCungCap(int MaNCC)
        {
            try
            {
                DataTable dt = new DataTable();
                ParameterBuilder pd = DBFactory.CreateParamBuilder();
                pd.AddParameter("MaNCC", MaNCC);

                dt = DBFactory.Database.FillDataTable("MatHang_Find_NhaCungCap", pd.Parameters);
                return dt;
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Hàm tìm mặt hàng theo hóa đơn bán hàng
        /// </summary>
        /// <param name="MaHoaDonBan"></param>
        /// <returns></returns>
        public DataTable Find_MaHoaDonBan(int MaHoaDonBan)
        {
            try
            {
                DataTable dt = new DataTable();
                ParameterBuilder pd = DBFactory.CreateParamBuilder();
                pd.AddParameter("MaHoaDonBan", MaHoaDonBan);

                dt = DBFactory.Database.FillDataTable("MatHang_Find_MaHoaDonBan", pd.Parameters);
                return dt;
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Hàm tìm mặt hàng theo mã hóa đơn nhập hàng
        /// </summary>
        /// <param name="MaHoaDonNhap"></param>
        /// <returns></returns>
        public DataTable Find_MaHoaDonNhap(int MaHoaDonNhap)
        {
            try
            {
                DataTable dt = new DataTable();
                ParameterBuilder pd = DBFactory.CreateParamBuilder();
                pd.AddParameter("MaHoaDonNhap", MaHoaDonNhap);

                dt = DBFactory.Database.FillDataTable("MatHang_Find_MaHoaDonNhap", pd.Parameters);
                return dt;
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Hàm tìm mặt hàng theo phiếu xuất kho trả hàng
        /// </summary>
        /// <param name="MaPhieuXuatKhoTraHang"></param>
        /// <returns></returns>
        public DataTable Find_MaPhieuXuatKhoTraHang(int MaPhieuXuatKhoTraHang)
        {
            try
            {
                DataTable dt = new DataTable();
                ParameterBuilder pd = DBFactory.CreateParamBuilder();
                pd.AddParameter("MaPhieuXuatKhoTraHang", MaPhieuXuatKhoTraHang);

                dt = DBFactory.Database.FillDataTable("MatHang_Find_MaPhieuXuatKhoTraHang", pd.Parameters);
                return dt;
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Hàm tìm mặt hàng theo phiếu bảo hành sửa chữa
        /// </summary>
        /// <param name="MaPhieuBaoHanhSuaChua"></param>
        /// <returns></returns>
        public DataTable Find_MaPhieuBaoHanhSuaChua(int MaPhieuBaoHanhSuaChua)
        {
            try
            {
                DataTable dt = new DataTable();
                ParameterBuilder pd = DBFactory.CreateParamBuilder();
                pd.AddParameter("MaPhieuBaoHanhSuaChua", MaPhieuBaoHanhSuaChua);

                dt = DBFactory.Database.FillDataTable("MatHang_Find_MaPhieuBaoHanhSuaChua", pd.Parameters);
                return dt;
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Hàm tìm mặt hàng theo mã loại mặt hàng của mặt hàng đó
        /// </summary>
        /// <param name="MaLoaiMH"></param>
        /// <param name="View"></param>
        /// <returns></returns>
        public DataTable Find_MaLoaiMH(int MaLoaiMH, bool View)
        {
            try
            {
                DataTable dt = new DataTable();
                ParameterBuilder pd = DBFactory.CreateParamBuilder();
                pd.AddParameter("MaLoaiMH", MaLoaiMH);
                pd.AddParameter("View", View);

                dt = DBFactory.Database.FillDataTable("MatHang_Find_MaLoaiMH", pd.Parameters);
                return dt;
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Hàm tìm mặt hàng theo tên loại mặt hàng
        /// </summary>
        /// <param name="TenLoaiMH"></param>
        /// <param name="View"></param>
        /// <returns></returns>
        public DataTable Find_TenLoaiMH(string TenLoaiMH, bool View)
        {
            try
            {
                DataTable dt = new DataTable();
                ParameterBuilder pd = DBFactory.CreateParamBuilder();
                pd.AddParameter("TenLoaiMH", TenLoaiMH);
                pd.AddParameter("View", View);

                dt = DBFactory.Database.FillDataTable("MatHang_Find_TenLoaiMH", pd.Parameters);
                return dt;
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Hàm tìm mặt hàng theo mã model mặt hàng
        /// </summary>
        /// <param name="MaModelMH"></param>
        /// <param name="View"></param>
        /// <returns></returns>
        public DataTable Find_MaModelMH(int MaModelMH, bool View)
        {
            try
            {
                DataTable dt = new DataTable();
                ParameterBuilder pd = DBFactory.CreateParamBuilder();
                pd.AddParameter("MaModelMH", MaModelMH);
                pd.AddParameter("View", View);

                dt = DBFactory.Database.FillDataTable("MatHang_Find_ModelMH", pd.Parameters);
                return dt;
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="MaModelMH"></param>
        /// <param name="View"></param>
        /// <returns></returns>
        public DataTable Find_MaModelMH_MaBanHang(int MaModelMH, int MaBanHang, bool View)
        {
            try
            {
                DataTable dt = new DataTable();
                ParameterBuilder pd = DBFactory.CreateParamBuilder();
                pd.AddParameter("MaModelMH", MaModelMH);
                pd.AddParameter("MaBanHang", MaBanHang);
                pd.AddParameter("View", View);

                dt = DBFactory.Database.FillDataTable("MatHang_Find_MaModelMH_MaBanHang", pd.Parameters);
                return dt;
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="MaModelMH"></param>
        /// <param name="MaNhapHang"></param>
        /// <param name="View"></param>
        /// <returns></returns>
        public DataTable Find_MaModelMH_MaNhapHang(int MaModelMH, int MaNhapHang, bool View)
        {
            try
            {
                DataTable dt = new DataTable();
                ParameterBuilder pd = DBFactory.CreateParamBuilder();
                pd.AddParameter("MaModelMH", MaModelMH);
                pd.AddParameter("MaNhapHang", MaNhapHang);
                pd.AddParameter("View", View);

                dt = DBFactory.Database.FillDataTable("MatHang_Find_MaModelMH_MaNhapHang", pd.Parameters);
                return dt;
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="MaModelMH"></param>
        /// <param name="MaXuatKhoTraHang"></param>
        /// <param name="View"></param>
        /// <returns></returns>
        public DataTable Find_MaModelMH_MaXuatKhoTraHang(int MaModelMH, int MaXuatKhoTraHang, bool View)
        {
            try
            {
                DataTable dt = new DataTable();
                ParameterBuilder pd = DBFactory.CreateParamBuilder();
                pd.AddParameter("MaModelMH", MaModelMH);
                pd.AddParameter("MaXuatKhoTraHang", MaXuatKhoTraHang);
                pd.AddParameter("View", View);

                dt = DBFactory.Database.FillDataTable("MatHang_Find_MaModelMH_MaXuatKhoTraHang", pd.Parameters);
                return dt;
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="MaModelMH"></param>
        /// <param name="Serial"></param>
        /// <param name="View"></param>
        /// <returns></returns>
        public DataTable Find_Serial_Model(int MaModelMH, string Serial, bool View)
        {
            try
            {
                DataTable dt = new DataTable();
                ParameterBuilder pd = DBFactory.CreateParamBuilder();

                pd.AddParameter("MaModelMH", MaModelMH);
                pd.AddParameter("Serial_Code", Serial);
                pd.AddParameter("View", View);

                dt = DBFactory.Database.FillDataTable("MatHang_Find_Serial_Model", pd.Parameters);
                return dt;
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="MaKH"></param>
        /// <returns></returns>
        public DataTable Find_MaKhachHang(int MaKH, bool View)
        {
            try
            {
                DataTable dt = new DataTable();
                ParameterBuilder pd = DBFactory.CreateParamBuilder();

                pd.AddParameter("MaKH", MaKH);
                pd.AddParameter("View", View);

                dt = DBFactory.Database.FillDataTable("MatHang_Find_MaKhachHang", pd.Parameters);
                return dt;
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="MaMH"></param>
        /// <param name="Kieu"></param>
        /// <returns></returns>
        public int Count_BHSC(int MaMH, int Kieu)
        {
            try
            {
                ParameterBuilder pd = DBFactory.CreateParamBuilder();

                pd.AddParameter("MaMH", MaMH);
                pd.AddParameter("Loai", Kieu);

                return (int)DBFactory.Database.ExecuteScalar("MatHang_CountBHSC", pd.Parameters);
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Hàm xóa 1 mặt hàng
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Delete(int id)
        {
            try
            {
                ParameterBuilder pd = DBFactory.CreateParamBuilder();
                pd.AddParameter("ID", id);

                return DBFactory.Database.ExecuteNonQuery("MatHang_Delete", pd.Parameters) > 0;
            }
            catch (Exception ex)
            {
                Logger.Write(ex);
                return false;
            }
        }

        /// <summary>
        /// Hàm chuyển DataTable thành 1 List MatHangEntity
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public List<MatHangEntity> ConvertToList(DataTable dt)
        {
            List<MatHangEntity> list = new List<MatHangEntity>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                int id = (int)dt.Rows[i][0];
                int mamodel = (int)dt.Rows[i][1];
                int mahoadonnhap = (int)dt.Rows[i][2];
                int mahoadonban = 0;
                int maxuat = 0;
                int mabhsc = 0;
                try
                {
                    mahoadonban = (int)dt.Rows[i][3];
                }
                catch
                {
                    mahoadonban = 0;
                }

                try
                {
                    maxuat = (int)dt.Rows[i][4];
                }
                catch
                {
                    maxuat = 0;
                }

                try
                {
                    mabhsc = (int)dt.Rows[i][5];
                }
                catch
                {
                    mabhsc = 0;
                }


                string serial = dt.Rows[i][6].ToString();

                int tinhtrang = (int)dt.Rows[i][7];

                MatHangEntity entity = new MatHangEntity(id, mamodel, mahoadonnhap, mahoadonban, maxuat, serial, mabhsc, tinhtrang);
                list.Add(entity);
            }
            return list;
        }
    }
}
